package br.com.portalecbahia.dao;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import br.com.portalecbahia.model.News;
import br.com.portalecbahia.util.PortalECBahiaUtil;

public class NewsDAO {

	private Context context;
	private SQLiteDatabase database;
	private DBHelper dbHelper;

	public NewsDAO(Context context) {
		this.context = context;
	}

	public NewsDAO openWritable() throws SQLException {
		dbHelper = new DBHelper(context);
		database = dbHelper.getWritableDatabase();
		return this;
	}
	public NewsDAO openReadable() throws SQLException {
		dbHelper = new DBHelper(context);
		database = dbHelper.getReadableDatabase();
		return this;
	}
	

	public void close() {
		dbHelper.close();
	}
	
	
	public void insert(JSONArray jsonArray) throws JSONException, ParseException {
		this.openWritable();
		Cursor cursor = null;
		try{
			database.beginTransaction();
			for (int i = 0; i < jsonArray.length(); i++) {
				JSONObject json = jsonArray.getJSONObject(i);
				
				cursor = database.rawQuery(" INSERT INTO tbl_news (ID,  title, urlImage, date, content, urlWebSite)  VALUES (" +
								"" + json.getLong("id")            +",  " +
				         		"'"+json.getString("title")        +"', "+
				         		"'"+json.getString("urlImage")     +"', " +
								"'"+json.getString("date") 	       +"', "+
								"'"+ json.getString("content") 	   +"', " +
								"'"+json.getString("urlWebSite")   +"');", null);
					
					cursor.moveToFirst();
					cursor.close();
				
					
				
			}
		}catch (SQLException e) {
			Log.e(PortalECBahiaUtil.TAG, e.toString());
		}finally {
			
			database.setTransactionSuccessful();
			database.endTransaction();
			
		}
		
		this.close();
		
	}
	
	public List<News> findAll () {
		this.openReadable();

		List<News> result = new ArrayList<News>();		
		Cursor cursor;
			 
		try{
			cursor = database.rawQuery("SELECT position, ID," +
									   "title, " +
									   "urlImage, " +
									   "date, " +
									   "content," +
									   "urlWebSite " +
									   "FROM tbl_news " +
									   "order by date desc limit 0,20", null);
			cursor.moveToFirst();
			
			if (!cursor.isAfterLast()){
				do{
					result.add(this.bindSQLite(cursor));
				}while (cursor.moveToNext());
			}
			
			cursor.close();
		}catch (SQLException e){
			Log.e(PortalECBahiaUtil.TAG, e.toString());
		}
		
		this.close();
		
		return result;
	}
	
	public void getLimits(){

		this.openReadable();

		Cursor cursor;
			 
		try{
			cursor = database.rawQuery("select max(position) AS ID_MAX from tbl_news  union  select TAB.POSITION AS ID_MAX FROM (select position from tbl_news order by position asc limit 0,1) as TAB", null);
			cursor.moveToFirst();
			

			if (!cursor.isAfterLast()){
				int i = 0;
				do{
					if(i==0){
						PortalECBahiaUtil.LIMIT_MIN =  cursor.getLong(0);
						i++;
					}else {
						PortalECBahiaUtil.LIMIT_MAX =  cursor.getLong(0);

					}
				
				}while (cursor.moveToNext());
			}
			
			
			cursor.close();
		}catch (SQLException e){
			Log.e(PortalECBahiaUtil.TAG, e.toString());
		}
		
		this.close();
	}
	
	public News findByPrimaryKey(Long position) {
		this.openReadable();

		News result = null;		
		Cursor cursor;
			 
		try{
			cursor = database.rawQuery("SELECT position, ID," +
					   "title, " +
					   "urlImage, " +
					   "date, " +
					   "content, " +
					   "urlWebSite "+
					   "FROM tbl_news " +
					   "WHERE position = "+position+"", null);
			cursor.moveToFirst();
			
			if (!cursor.isAfterLast())
				result = this.bindSQLite(cursor);
			
			cursor.close();
		}catch (SQLException e){
			Log.e(PortalECBahiaUtil.TAG, e.toString());
		}
		
		this.close();
		return result;
	}
	
	
	
	public void removeAll() {
		this.openWritable();
		Cursor cursor = null;
		try{
			cursor = database.rawQuery("DELETE FROM tbl_news", null);
			cursor.moveToFirst();
			cursor.close();
		}catch (SQLException e) {
			Log.e(PortalECBahiaUtil.TAG, e.toString());
		}
		this.close();
		
	}
	
	private News bindSQLite(Cursor cursor){
		News news  = new News();
		SimpleDateFormat dateFormat = new  SimpleDateFormat("yyyy-MM-dd HH:mm");
		for (int i = 0; i < cursor.getColumnCount(); i++){
			if("position".equals(cursor.getColumnName(i)))
				news.setPosition(cursor.getLong(i));
			if("ID".equals(cursor.getColumnName(i)))
				news.setId(cursor.getLong(i));
			else if("title".equals(cursor.getColumnName(i))) 
				news.setTitle(cursor.getString(i));
			else if("urlImage".equals(cursor.getColumnName(i))) 
				news.setUrlImage(cursor.getString(i));
			else if("date".equals(cursor.getColumnName(i)))
				try {	
					news.setDate(dateFormat.parse(cursor.getString(i)));
				} catch (ParseException e) {
					Log.e(PortalECBahiaUtil.TAG, e.getMessage());
				}
			else if("content".equals(cursor.getColumnName(i))) 
				news.setContent(cursor.getString(i));
			
			else if("urlWebSite".equals(cursor.getColumnName(i))) 
				news.setUrlWebsite(cursor.getString(i));
		}
		return news;
	}


}
